﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CuaHangLaptop.BIZ;

namespace CuaHangLaptop.DAL
{
    class ChiTietKho_DAL
    {
        internal static int Get_Lasted_ID()
        {
            int id = 0;
            SqlConnection conn = Connection.Connection_String();
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("select id from ChiTietKho order by id desc", conn);
                rdr = cmd.ExecuteReader();
                rdr.Read();
                id = rdr.GetInt32(0);
            }
            catch
            {
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return id;
        }

        internal static int Get_TonKho_Now(string _malaptop, DateTime _dt)
        {
            int tonkho = 0;
            SqlConnection conn = Connection.Connection_String();
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                String s = String.Format("select top(1) tonkho from ChiTietKho where Malaptop='{0}' and ngay<'{1}' order by id desc", _malaptop, _dt);
                SqlCommand cmd = new SqlCommand(s, conn);
                rdr = cmd.ExecuteReader();
                rdr.Read();
                tonkho = int.Parse(rdr["tonkho"].ToString());
                
            }
            catch
            {
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return tonkho;
        }

        internal static int Get_TonKho_Now(string _malaptop)
        {
            int tonkho = 0;
            SqlConnection conn = Connection.Connection_String();
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                String s = String.Format("select top(1) tonkho from ChiTietKho where  Malaptop='{0}' order by id desc", _malaptop);
                SqlCommand cmd = new SqlCommand(s, conn);
                rdr = cmd.ExecuteReader();
                rdr.Read();
                tonkho = int.Parse( rdr["tonkho"].ToString());
            }
            catch
            {
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return tonkho;
        }

        internal static int Get_TonKho(string _malaptop, DateTime _date1, DateTime _date2)
        {
            int tonkho = 0;
            SqlConnection conn = Connection.Connection_String();
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                String s = String.Format("select tonkho from ChiTietKho where  Malaptop='{0}' and Ngay>='{1}' and Ngay<='{2}' order by Ngay desc", _malaptop, _date1, _date2);
                SqlCommand cmd = new SqlCommand(s, conn);
                rdr = cmd.ExecuteReader();
                rdr.Read();
                tonkho = rdr.GetInt32(0);
            }
            catch
            {
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return tonkho;
        }

        // LẤY CHI TIẾT KHO CÓ MA = CTK.MA
        internal static ChiTietKho_BIZ Get_CTKho_ByCode(string _MaCTKho)
        {
            ChiTietKho_BIZ ctk1;
            SqlConnection conn = Connection.Connection_String();
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                String s = String.Format("select * from ChiTietKho where  MaCTKho='{0}'", _MaCTKho);
                SqlCommand cmd = new SqlCommand(s, conn);
                rdr = cmd.ExecuteReader();
                rdr.Read();
                ctk1 = new ChiTietKho_BIZ(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetDateTime(3), rdr.GetInt32(4));
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return ctk1;
        }

        // LẤY TẤT CẢ NHÀ SẢN XUẤT CÓ MANSX = NSX.MANSX
        internal static List<ChiTietKho_BIZ> GetAll_CTKho_ByLaptop(string _Malaptop)
        {
            List<ChiTietKho_BIZ> result = new List<ChiTietKho_BIZ>();
            SqlConnection conn = Connection.Connection_String();
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                String s = String.Format("select * from ChiTietKho where Malaptop='{0}'", _Malaptop);
                SqlCommand cmd = new SqlCommand(s, conn);
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    ChiTietKho_BIZ dd = new ChiTietKho_BIZ(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetDateTime(3), rdr.GetInt32(4));
                    result.Add(dd);
                }
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return result;
        }

        // DELETE
        internal static void Delete(string _mactkho)
        {
            SqlConnection conn = Connection.Connection_String();
            try
            {
                conn.Open();
                String s = String.Format("delete from ChiTietKho where maCTKho='{0}'", _mactkho);
                SqlCommand cmd = new SqlCommand(s, conn);

                if (cmd.ExecuteNonQuery() > 0)
                {
                }
            }
            finally
            {
                conn.Close();
            }
        }

        // UPDATE
        internal static void Update(ChiTietKho_BIZ ctk)
        {
            SqlConnection conn = Connection.Connection_String();
            try
            {
                conn.Open();
                String s = String.Format("update ChiTietKho set MaCTKho = '{0}', MaLapTop = '{1}', Ngay = '{2}', TonKho = {3} where id={4}", ctk.MaCTKho, ctk.Laptop.MaLaptop, ctk.Ngay, ctk.Tonkho, ctk.ID);
                SqlCommand cmd = new SqlCommand(s, conn);

                if (cmd.ExecuteNonQuery() > 0)
                {
                }
            }
            finally
            {
                conn.Close();
            }
        }

        // INSERT
        internal static int Insert(ChiTietKho_BIZ ctk)
        {
            int newId = -1;
            SqlConnection conn = Connection.Connection_String();
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                String s = String.Format("insert into ChiTietKho values('{0}', '{1}','{2}', {3})", ctk.MaCTKho, ctk.Laptop.MaLaptop, ctk.Ngay, ctk.Tonkho);
                SqlCommand cmd = new SqlCommand(s, conn);

                if (cmd.ExecuteNonQuery() > 0)
                {
                    s = String.Format("select id from ChiTietKho where MaCTKho='{0}'", ctk.MaCTKho);
                    cmd.CommandText = s;
                    rdr = cmd.ExecuteReader();

                    if (rdr.HasRows)
                    {
                        rdr.Read();
                        newId = Int32.Parse(rdr["id"].ToString());
                    }
                }
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return newId;
        }
    }
}
